﻿using Model.Enums;

namespace Utils.SqlUtils
{
    public static class GetSql
    {
        /// <summary>
        /// 获取SqlServer表结构信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string GetSqlServerStructure(string tableName)
        {
            return $@" SELECT cast(a.colorder as int) {ColumnEnums.ColumnInfo.Sort},
                    cast(a.name as varchar) {ColumnEnums.ColumnInfo.Name},
                    (case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in
                    (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then cast(1 as bit)  else cast(0 as bit) end) {ColumnEnums.ColumnInfo.Key},
                    cast(b.name as varchar) ColumnType,cast(a.length as int)  {ColumnEnums.ColumnInfo.Size},
                    (case when a.isnullable=1 then cast(1 as bit) else cast(0 as bit) end) {ColumnEnums.ColumnInfo.IsNull},
                    isnull(g.[value], ' ') AS {ColumnEnums.ColumnInfo.CnName}
                    FROM syscolumns a
                    left join systypes b on a.xtype=b.xusertype
                    inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
                    left join syscomments e on a.cdefault=e.id
                    left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
                    left join sys.extended_properties f on d.id=f.class and f.minor_id=0
                    where b.name is not null
                    and d.name='{tableName}'
                    order by a.id,a.colorder";
        }

    }
}
